﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml.Linq;
using System.ServiceModel;
using Microsoft.Office.Tools.Excel;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using GLUpload2.LNPS_DynamicScriptService;
using GLUpload2.GeneralJournalService;

namespace GLUpload2
{
    public partial class Sheet6
    {
        const int startRow = 10;
        const int dateCol = 1;
        const int mainAcctCol = 2;
        const int mainAcctCostCentreCol = 3;
        const int mainAcctDepartmentCol = 4;
        const int descriptionCol = 5;
        const int debitCol = 6;
        const int creditCol = 7;
        const int offsetTypeCol = 8;
        const int offsetAcctCol = 9;
        const int offsetAcctCostCentreCol = 10;
        const int offsetAcctDepartmentCol = 11;
        const int offsetDescriptionCol = 12;
        const int messageCol = 13;

        Lookup lookup = new Lookup();
        
        private void Sheet6_Startup(object sender, System.EventArgs e)
        {
            // Worksheets
            Excel.Worksheet DataWorksheet = ((Excel.Worksheet)Application.Worksheets["Data"]);

            // Ranges
            Excel.Range Company = DataWorksheet.Range["Company"];
            
            lookup.Company = Company.Value;
            lookup.ExcelParent = this.Application;
            lookup.Hide();
        }

        private void Sheet6_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Upload.Click += new System.EventHandler(this.Upload_Click);
            this.SelectionChange += new Microsoft.Office.Interop.Excel.DocEvents_SelectionChangeEventHandler(this.Sheet6_SelectionChange);
            this.Startup += new System.EventHandler(this.Sheet6_Startup);
            this.Shutdown += new System.EventHandler(this.Sheet6_Shutdown);

        }

        #endregion

        private void Sheet6_SelectionChange(Excel.Range Target)
        {
            // Worksheets
            Excel.Worksheet activeSheet = ((Excel.Worksheet)Application.ActiveSheet);
            try
            {
                bool showLookup = false;
                if (Target.Row >= startRow)
                {
                    if (Target.Column == mainAcctCol)
                    {
                        lookup.ChangeTab("GLAccounts", Target.Value);
                        showLookup = true;
                    }
                    if (Target.Column == offsetAcctCol)
                    {
                        // Ranges
                        Excel.Range OffsetType = activeSheet.Cells[Target.Row, Target.Column - 1];
                        if (OffsetType.Value == "Bank")
                        {
                            lookup.ChangeTab("BankAccounts", Target.Value);
                            showLookup = true;
                        }
                        if (OffsetType.Value == "Ledger")
                        {
                            lookup.ChangeTab("OffsetAccounts", Target.Value);
                            showLookup = true;
                        }
                    }
                    if (Target.Column == mainAcctCostCentreCol || Target.Column == offsetAcctCostCentreCol)
                    {
                        lookup.ChangeTab("CostCentres", Target.Value);
                        showLookup = true;
                    }
                    if (Target.Column == mainAcctDepartmentCol || Target.Column == offsetAcctDepartmentCol)
                    {
                        lookup.ChangeTab("Departments", Target.Value);
                        showLookup = true;
                    }
                }
                if (showLookup)
                {
                    Excel.Range range = Target;
                    int zoom = Convert.ToInt32(range.Application.ActiveWindow.Zoom);
                    int relativeLeft = 0;
                    int relativeTop = 0;

                    for (int index = 1; index < range.Column; index++)
                    {
                        relativeLeft += Convert.ToInt32(Math.Round(
                        (double)((Microsoft.Office.Interop.Excel.Range)range.Worksheet.Cells[1, index]).Width * 4 * zoom / 300,
                        MidpointRounding.AwayFromZero));
                    }
                    int left = range.Application.ActiveWindow.PointsToScreenPixelsX(relativeLeft);

                    // The formula for row height is similar to the one for column width.
                    for (int index = 1; index <= range.Row; index++)
                    {
                        relativeTop += Convert.ToInt32(Math.Round(
                        (double)((Microsoft.Office.Interop.Excel.Range)range.Worksheet.Cells[index, 1]).Height * 4 * zoom / 300,
                        MidpointRounding.AwayFromZero));
                    }
                    int top = range.Application.ActiveWindow.PointsToScreenPixelsY(relativeTop);

                    lookup.Show();
                    lookup.Top = top;
                    lookup.Left = left;

                    IntPtr h = APIUtils.GetXLMainWindowHandle(APIUtils.GetDesktopWindow());
                    APIUtils.SetFocus(h);
                }
                else
                {
                    lookup.Hide();
                }
            }
            catch { }
        }

        private void Upload_Click(object sender, EventArgs e)
        {
            // Aif Service Client
            GeneralJournalServiceClient client = new GeneralJournalServiceClient();
            
            // Create an instance of the CallContext class.
            GeneralJournalService.CallContext context = new GeneralJournalService.CallContext();

            // Worksheets
            Excel.Worksheet activeSheet = ((Excel.Worksheet)Application.ActiveSheet);
            Excel.Worksheet DataWorksheet = ((Excel.Worksheet)Application.Worksheets["Data"]);

            // Ranges
            Excel.Range Company = DataWorksheet.Range["Company"];
            Excel.Range JournalName = DataWorksheet.Range["JournalName"];
            Excel.Range JournalCurrency = DataWorksheet.Range["JournalCurrency"];
            Excel.Range SumOfDebits = DataWorksheet.Range["SumOfDebits"];
            Excel.Range SumOfCredits = DataWorksheet.Range["SumOfCredits"];
            Excel.Range Message = DataWorksheet.Range["Message"];
            Excel.Range UploadMessages = DataWorksheet.Range["UploadMessages"];
            Excel.Range currentCell;

            // Set context company and language
            context.Company = Company.Value;
            context.Language = "en-gb";

            // Working variables
            int currentRow = startRow;
            string currentCellValue = "";
            decimal Debits = 0, Credits = 0;
            string JournalRef = "General journal uploaded from Excel " + DateTime.UtcNow.ToString();

            // Clear messages from previous run
            UploadMessages.ClearContents();

            // Commencement message
            Message.Value = "Running...";
            Application.ScreenUpdating = true;

            // Switch on hourglass during upload
            Application.Cursor = Excel.XlMousePointer.xlWait;

            // Create journal header
            AxdLedgerGeneralJournal journal = new AxdLedgerGeneralJournal();
            AxdEntity_LedgerJournalTable journalHeader = new AxdEntity_LedgerJournalTable();

            journalHeader.JournalName = JournalName.Value;
            journalHeader.Name = JournalRef;
            journalHeader.DetailSummaryPostingSpecified = true;

            // Creat journal lines
            List<AxdEntity_LedgerJournalTrans> journalLines = new List<AxdEntity_LedgerJournalTrans>();

            currentCell = (Excel.Range)activeSheet.Cells[currentRow, dateCol];
            try { currentCellValue = currentCell.Value2.ToString(); }
            catch { }
            while (currentCellValue != "")
            {
                AxdEntity_LedgerJournalTrans journalLine = new AxdEntity_LedgerJournalTrans();
                
                // Message
                Excel.Range messageCell = (Excel.Range)activeSheet.Cells[currentRow, messageCol];
                
                // Date
                Excel.Range dateCell = (Excel.Range)activeSheet.Cells[currentRow, dateCol];
                dateCell.Activate();
                DateTime dateCellValue = DateTime.Now;
                try 
                { 
                    dateCellValue = dateCell.Value; 
                }
                catch 
                {
                    messageCell.Value = "Invalid date specified.";
                    goto exitUpload_Click;
                }
                journalLine.TransDate = dateCellValue;
                journalLine.TransDateSpecified = true;

                // Currency code
                journalLine.CurrencyCode = JournalCurrency.Value;

                // Description
                Excel.Range descriptionCell = (Excel.Range)activeSheet.Cells[currentRow, descriptionCol];
                string descriptionCellValue = "";
                try 
                {
                    descriptionCellValue = (descriptionCell.Value == null) ? "" : descriptionCell.Value; 
                }
                catch 
                { 
                    descriptionCellValue = "";
                }
                journalLine.Txt = descriptionCellValue;

                // Main account
                AxdType_MultiTypeAccount account = new AxdType_MultiTypeAccount();
                journalLine.Company = Company.Value;
                journalLine.AccountType = AxdEnum_LedgerJournalACType.Ledger;
                journalLine.AccountTypeSpecified = true;
                Excel.Range accountCell = (Excel.Range)activeSheet.Cells[currentRow, mainAcctCol];
                string accountCellValue = "";
                try 
                { 
                    accountCellValue = (accountCell.Value == null) ? "" : accountCell.Value; 
                }
                catch 
                {
                    accountCellValue = "";
                }
                if (accountCellValue == "")
                {
                    messageCell.Value = "GL account not specified.";
                    goto exitUpload_Click;
                }
                Excel.Range accountCostCentreCell = (Excel.Range)activeSheet.Cells[currentRow, mainAcctCostCentreCol];
                string accountCostCentreCellValue = "";
                try 
                { 
                    accountCostCentreCellValue = (accountCostCentreCell.Value == null) ? "" : accountCostCentreCell.Value; 
                }
                catch 
                {
                    accountCostCentreCellValue = "";
                }
                Excel.Range accountDepartmentCell = (Excel.Range)activeSheet.Cells[currentRow, mainAcctDepartmentCol];
                string accountDepartmentCellValue = "";
                try 
                { 
                    accountDepartmentCellValue = (accountDepartmentCell.Value == null) ? "" : accountDepartmentCell.Value; 
                }
                catch 
                {
                    accountDepartmentCellValue = "";
                }
                account.Account = accountCellValue;
                account.DisplayValue = accountCellValue + "-" + accountCostCentreCellValue + "-" + accountDepartmentCellValue;

                List<AxdType_DimensionAttributeValue> accountDimensions = new List<AxdType_DimensionAttributeValue>();
                if (accountCostCentreCellValue != "")
                {
                    AxdType_DimensionAttributeValue dimValue = new AxdType_DimensionAttributeValue();
                    dimValue.Name = "CostCentre";
                    dimValue.Value = accountCostCentreCellValue;
                    accountDimensions.Add(dimValue);
                }
                if (accountDepartmentCellValue != "")
                {
                    AxdType_DimensionAttributeValue dimValue = new AxdType_DimensionAttributeValue();
                    dimValue.Name = "Department";
                    dimValue.Value = accountDepartmentCellValue;
                    accountDimensions.Add(dimValue);
                }
                account.Values = accountDimensions.ToArray();
                journalLine.LedgerDimension = account;

                // Offset account
                Excel.Range offsetTypeCell = (Excel.Range)activeSheet.Cells[currentRow, offsetTypeCol];
                string offsetTypeCellValue = "";
                try
                {
                    offsetTypeCellValue = (offsetTypeCell.Value == null) ? "" : offsetTypeCell.Value;
                }
                catch
                {
                    offsetTypeCellValue = "";
                }
                Excel.Range offsetCell = (Excel.Range)activeSheet.Cells[currentRow, offsetAcctCol];
                string offsetCellValue = "";
                try
                {
                    offsetCellValue = (offsetCell.Value == null) ? "" : offsetCell.Value;
                }
                catch
                {
                    offsetCellValue = "";
                }
                Excel.Range offsetCostCentreCell = (Excel.Range)activeSheet.Cells[currentRow, offsetAcctCostCentreCol];
                string offsetCostCentreCellValue = "";
                try
                {
                    offsetCostCentreCellValue = (offsetCostCentreCell.Value == null) ? "" : offsetCostCentreCell.Value;
                }
                catch
                {
                    offsetCostCentreCellValue = "";
                }
                Excel.Range offsetDepartmentCell = (Excel.Range)activeSheet.Cells[currentRow, offsetAcctDepartmentCol];
                string offsetDepartmentCellValue = "";
                try
                {
                    offsetDepartmentCellValue = (offsetDepartmentCell.Value == null) ? "" : offsetDepartmentCell.Value;
                }
                catch
                {
                    offsetDepartmentCellValue = "";
                }

                if (offsetCellValue != "")
                {
                    // Description
                    Excel.Range offsetDescriptionCell = (Excel.Range)activeSheet.Cells[currentRow, offsetDescriptionCol];
                    string offsetDescriptionCellValue = "";
                    try
                    {
                        offsetDescriptionCellValue = (offsetDescriptionCell.Value == null) ? "" : offsetDescriptionCell.Value;
                    }
                    catch
                    {
                        offsetDescriptionCellValue = "";
                    }
                    journalLine.OffsetTxt = offsetDescriptionCellValue;

                    AxdType_MultiTypeAccount offset = new AxdType_MultiTypeAccount();
                    journalLine.OffsetCompany = Company.Value;

                    if (offsetTypeCellValue == "Bank")
                    {
                        journalLine.OffsetAccountType = AxdEnum_LedgerJournalACType.Bank;
                        journalLine.OffsetAccountTypeSpecified = true;

                        offsetCostCentreCell.Value = "";
                        offsetDepartmentCell.Value = "";

                        offset.Account = offsetCellValue;
                        offset.DisplayValue = offsetCellValue;
                    }
                    if (offsetTypeCellValue == "Ledger")
                    {
                        journalLine.OffsetAccountType = AxdEnum_LedgerJournalACType.Ledger;
                        journalLine.OffsetAccountTypeSpecified = true;

                        offset.Account = offsetCellValue;
                        offset.DisplayValue = offsetCellValue + "-" + offsetCostCentreCellValue + "-" + offsetDepartmentCellValue;

                        List<AxdType_DimensionAttributeValue> offsetDimensions = new List<AxdType_DimensionAttributeValue>();
                        if (offsetCostCentreCellValue != "")
                        {
                            AxdType_DimensionAttributeValue dimValue = new AxdType_DimensionAttributeValue();
                            dimValue.Name = "CostCentre";
                            dimValue.Value = offsetCostCentreCellValue;
                            offsetDimensions.Add(dimValue);
                        }
                        if (offsetDepartmentCellValue != "")
                        {
                            AxdType_DimensionAttributeValue dimValue = new AxdType_DimensionAttributeValue();
                            dimValue.Name = "Department";
                            dimValue.Value = offsetDepartmentCellValue;
                            offsetDimensions.Add(dimValue);
                        }
                        offset.Values = offsetDimensions.ToArray();
                    }
                    journalLine.OffsetLedgerDimension = offset;
                }

                // Debit
                Excel.Range debitCell = (Excel.Range)activeSheet.Cells[currentRow, debitCol];
                decimal debitCellValue = 0;
                try { debitCellValue = (decimal)debitCell.Value; }
                catch { };
                if (debitCellValue != 0)
                {
                    journalLine.AmountCurDebit = debitCellValue;
                    journalLine.AmountCurDebitSpecified = true;

                    Debits += debitCellValue;
                    if (offsetCellValue != "") Credits += debitCellValue;
                }

                // Credit
                Excel.Range creditCell = (Excel.Range)activeSheet.Cells[currentRow, creditCol];
                decimal creditCellValue = 0;
                try { creditCellValue = (decimal)creditCell.Value; }
                catch { };
                if (creditCellValue != 0)
                {
                    journalLine.AmountCurCredit = creditCellValue;
                    journalLine.AmountCurCreditSpecified = true;

                    Credits += creditCellValue;
                    if (offsetCellValue != "") Debits += creditCellValue;
                }

                if (creditCellValue == 0 && debitCellValue == 0)
                {
                    messageCell.Value = "Credit or debit not specified";
                    goto exitUpload_Click;
                }

                // Add journal line
                journalLines.Add(journalLine);
                messageCell.Value = "Processed";

                // Increment row
                currentRow += 1;
                currentCell = (Excel.Range)activeSheet.Cells[currentRow, dateCol];
                currentCellValue = "";
                try { currentCellValue = currentCell.Value2.ToString(); }
                catch { };
            }
            // Link objects in reverse order from child to parent
            journalHeader.LedgerJournalTrans = journalLines.ToArray();            
            journal.LedgerJournalTable = new AxdEntity_LedgerJournalTable[1] { journalHeader };
            
            // check debits balance with credits
            SumOfDebits.Value = Debits;
            SumOfCredits.Value = Credits;
            if (Debits != Credits)
            {
                Message.Value = "Credits and debits do not balance. Journal not created.";
                goto exitUpload_Click;
            }

            // Attempt to create journal
            try
            {
                Message.Activate();
                client.create(context, journal);
                AxdLedgerGeneralJournal foundgeneralJournal = client.find(context, createQueryCriteria(JournalRef)); ;
                Message.Value = string.Format("Journal {0} created.", foundgeneralJournal.LedgerJournalTable[0].JournalNum);
            }

            catch (FaultException<GeneralJournalService.AifFault> ex)
            {
                foreach (var exceptionLog in ex.Detail.InfologMessageList)
                {
                    string errorMessage = exceptionLog.Message;
                    int startError = (errorMessage.LastIndexOf("]") + 1);
                    errorMessage = exceptionLog.Message.Substring(startError, exceptionLog.Message.Length - startError);
                    MessageBox.Show(string.Format("{0}: {1}", exceptionLog.InfologMessageType, errorMessage),"Upload_Click");
                }
            }
        exitUpload_Click:
           // Reset hourglass
            Application.Cursor = Excel.XlMousePointer.xlDefault;

            // Close connection
            client.Close();
        }

        private static QueryCriteria createQueryCriteria(string journalValue)
        {
            CriteriaElement[] criteriaElements = new CriteriaElement[1];
            criteriaElements[0] = new CriteriaElement();
            criteriaElements[0].DataSourceName = "LedgerJournalTable";
            criteriaElements[0].FieldName = "Name";
            criteriaElements[0].Value1 = journalValue;
            QueryCriteria queryCriteria = new QueryCriteria();
            queryCriteria.CriteriaElement = criteriaElements;
            return queryCriteria;
        }
    }
}
